Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.Data.SqlClient
Public Class UserDAO
    Public Shared _TableName As String = "users"
    Protected Shared Function Insert(ByVal obj As User, ByVal iAutoKey As Boolean, ByVal bPassEncrypted As Boolean, ByVal con As SqlConnection, ByVal trans As SqlTransaction) As Boolean
        If obj Is Nothing Then
            Return False
        End If
        If iAutoKey Then
            obj.UserID = System.Guid.NewGuid.ToString()
        End If
        Try
            Dim sql As String = "INSERT INTO " & _TableName & " VALUES(@UserID, @DepartmentID, @LocationID, @Username, "
            sql &= IIf(Not bPassEncrypted, "HASHBYTES('MD5', '" & obj.Passwd & "'), ", "@Passwd, ")
            sql &= "@Title, @FullName, @Position, @Phone, @Mobile, @Email, @ActiveCode, @Active, @CompanyID, @ModifiedDate, @ModifiedID, @Synchronized, @Deleted);"
            Dim lParam As List(Of SqlParameter) = New List(Of SqlParameter)
            lParam.Add(New SqlParameter("@UserID", obj.UserID))
            lParam.Add(New SqlParameter("@DepartmentID", obj.DepartmentID))
            lParam.Add(New SqlParameter("@LocationID", obj.LocationID))
            lParam.Add(New SqlParameter("@Username", obj.UserName))
            lParam.Add(New SqlParameter("@Title", obj.Title))
            lParam.Add(New SqlParameter("@FullName", obj.FullName))
            If bPassEncrypted Then
                lParam.Add(New SqlParameter("@Passwd", obj.Passwd))
            End If
            lParam.Add(New SqlParameter("@Position", obj.Position))
            lParam.Add(New SqlParameter("@Phone", obj.Phone))
            lParam.Add(New SqlParameter("@Mobile", obj.Mobile))
            lParam.Add(New SqlParameter("@Email", obj.Email))
            lParam.Add(New SqlParameter("@ActiveCode", obj.ActiveCode))
            lParam.Add(New SqlParameter("@Active", obj.Active))
            lParam.Add(New SqlParameter("@CompanyID", obj.CompanyID))
            lParam.Add(New SqlParameter("@ModifiedDate", obj.ModifiedDate))
            lParam.Add(New SqlParameter("@ModifiedID", obj.ModifiedID))
            lParam.Add(New SqlParameter("@Synchronized", obj.Synchronized))
            lParam.Add(New SqlParameter("@Deleted", obj.Deleted))
            Dim lParamArray As SqlParameter() = lParam.ToArray()
            SqlHelper.ExecuteScalar(trans, CommandType.Text, sql, lParamArray)
            Return True
        Catch ex As Exception
            Throw New Exception(ex.Message, ex)
        Finally
        End Try
        Return False
    End Function
    Protected Shared Function Update(ByVal obj As User, ByVal bPassEncrypted As Boolean, ByVal con As SqlConnection, ByVal trans As SqlTransaction) As Boolean
        If obj Is Nothing Then
            Return False
        End If
        If obj.UserID = CONSTANT.USER_ADMINISTRATOR_ID Then
            If obj.ModifiedID <> CONSTANT.USER_ADMINISTRATOR_ID Then
                Return False
            End If
        End If
        Try
            Dim sql As String = ""
            sql = "UPDATE " & _TableName & " " & _
                  "SET department_id=@DepartmentID, location_id=@LocationID, username=@Username, "
            sql &= IIf(Not bPassEncrypted, "passwd=HASHBYTES('MD5', '" & obj.Passwd & "'), ", "passwd=@Passwd, ")
            sql &= "title=@Title, full_name=@FullName, position=@Position, phone=@Phone, mobile=@Mobile, email=@Email, active_code=@ActiveCode, active=@Active, " & _
                   "company_id=@CompanyID, modified_date=@ModifiedDate, modified_id=@ModifiedID, synchronized=@Synchronized, deleted=@Deleted " & _
                   "WHERE user_id=@UserID "
            Dim lParam As List(Of SqlParameter) = New List(Of SqlParameter)
            lParam.Add(New SqlParameter("@UserID", obj.UserID))
            lParam.Add(New SqlParameter("@DepartmentID", obj.DepartmentID))
            lParam.Add(New SqlParameter("@LocationID", obj.LocationID))
            lParam.Add(New SqlParameter("@Username", obj.UserName))
            If bPassEncrypted Then
                lParam.Add(New SqlParameter("@Passwd", obj.Passwd))
            End If
            lParam.Add(New SqlParameter("@Title", obj.Title))
            lParam.Add(New SqlParameter("@FullName", obj.FullName))
            lParam.Add(New SqlParameter("@Position", obj.Position))
            lParam.Add(New SqlParameter("@Phone", obj.Phone))
            lParam.Add(New SqlParameter("@Mobile", obj.Mobile))
            lParam.Add(New SqlParameter("@Email", obj.Email))
            lParam.Add(New SqlParameter("@ActiveCode", obj.ActiveCode))
            lParam.Add(New SqlParameter("@Active", obj.Active))
            lParam.Add(New SqlParameter("@CompanyID", obj.CompanyID))
            lParam.Add(New SqlParameter("@ModifiedDate", obj.ModifiedDate))
            lParam.Add(New SqlParameter("@ModifiedID", obj.ModifiedID))
            lParam.Add(New SqlParameter("@Synchronized", obj.Synchronized))
            lParam.Add(New SqlParameter("@Deleted", obj.Deleted))
            Dim lParamArray As SqlParameter() = lParam.ToArray()
            SqlHelper.ExecuteScalar(trans, CommandType.Text, sql, lParamArray)
            Return True
        Catch ex As Exception
            Throw New Exception(ex.Message, ex)
        Finally
        End Try
        Return False
    End Function
    Protected Shared Function ChangePassword(ByVal obj As User, ByVal con As SqlConnection, ByVal trans As SqlTransaction) As Boolean
        Return Update(obj, False, con, trans)
    End Function
    Protected Shared Function DeleteByID(ByVal _ID As String, ByVal con As SqlConnection, ByVal trans As SqlTransaction) As Boolean
        If _ID = CONSTANT.USER_ADMINISTRATOR_ID Then
            Return False
        End If
        Try
            Dim sql As String = "UPDATE " & _TableName & " SET deleted=1 " & _
                                "WHERE user_id=@UserID "
            Dim lParamArray As SqlParameter() = Nothing
            lParamArray = New SqlParameter() { _
                New SqlParameter("@UserID", _ID) _
            }
            SqlHelper.ExecuteScalar(trans, CommandType.Text, sql, lParamArray)
            Return True
        Catch ex As Exception
            Throw New Exception(ex.Message, ex)
        Finally
        End Try
        Return False
    End Function
    Public Shared Function exists(ByVal _ID As String, ByVal _UserName As String, ByVal _Email As String) As Boolean
        Dim con As SqlConnection = Nothing
        Dim reader As SqlDataReader = Nothing
        Dim index As Integer = 0
        Dim lParamArray As SqlParameter() = New SqlParameter(3) {}
        Dim sql As String = "SELECT * FROM " & _TableName & " " & _
                            "WHERE deleted=0 "
        If _UserName <> "" Then
            lParamArray(index) = New SqlParameter("@UserName", _UserName)
            sql &= "AND username = @UserName "
            index = index + 1
        End If
        If _Email <> "" Then
            lParamArray(index) = New SqlParameter("@Email", _Email)
            sql &= "AND email IS NOT NULL AND email <> '' AND email = @Email "
            index = index + 1
        End If
        If _ID <> "" Then
            sql &= "AND user_id <> @UserID "
            lParamArray(index) = New SqlParameter("@UserID", _ID)
            index = index + 1
        End If
        Try
            con = DBHelper.GetConnection1()
            reader = SqlHelper.ExecuteReader(con, CommandType.Text, sql, lParamArray)
            If reader.Read Then
                Dim strValue As New ArrayList()
                strValue = DBHelper.ReadValue(reader)
                Return True
            End If
        Catch ex As Exception
            Throw New Exception(ex.Message, ex)
        Finally
            If con IsNot Nothing Then
                con.Close()
                con.Dispose()
                con = Nothing
            End If
            If reader IsNot Nothing Then
                reader.Close()
                reader = Nothing
            End If
        End Try
        Return False
    End Function
    Private Shared _SEARCH_TEXT_CONDITION As String = "u.username LIKE @SearchText OR u.full_name LIKE @SearchText OR u.email LIKE @SearchText OR d.department_name LIKE @SearchText"
    Public Shared Function countByCriteria(ByVal _CompanyID As String, ByVal LocationID As String, ByVal SearchText As String, ByVal iActive As Integer) As Integer
        Dim con As SqlConnection = Nothing
        Dim reader As SqlDataReader = Nothing
        Dim lParamArray As SqlParameter() = New SqlParameter(3) {}
        Dim sql As String = ""
        sql = "SELECT COUNT(u.user_id) FROM users u " & _
              "LEFT OUTER JOIN " & DepartmentDAO._TableName & " d ON d.department_id=u.department_id " & _
              "LEFT OUTER JOIN " & LocationDAO._TableName & " l ON u.location_id=l.location_id " & _
              "WHERE u.deleted = 0 "
        If _CompanyID <> "" Then
            sql &= "AND u.company_id=@CompanyID "
            lParamArray(0) = New SqlParameter("@CompanyID", _CompanyID)
        End If
        If LocationID <> "" Then
            sql &= "AND u.location_id=@LocationID "
            lParamArray(1) = New SqlParameter("@LocationID", LocationID)
        End If
        If SearchText <> "" Then
            sql &= "AND (" & _SEARCH_TEXT_CONDITION & ") "
            lParamArray(2) = New SqlParameter("@SearchText", "%" & SearchText & "%")
        End If
        If iActive >= 0 Then
            If iActive = 0 Then
                sql &= "AND u.active=0 "
            Else
                sql &= "AND u.active=1 "
            End If
        End If
        Try
            con = DBHelper.GetConnection1()
            reader = SqlHelper.ExecuteReader(con, CommandType.Text, sql, lParamArray)
            If reader.Read Then
                Dim strValue As New ArrayList()
                strValue = DBHelper.ReadValue(reader)
                Return strValue(0)
            End If
        Catch ex As Exception
            Throw New Exception(ex.Message, ex)
        Finally
            If con IsNot Nothing Then
                con.Close()
                con.Dispose()
                con = Nothing
            End If
            If reader IsNot Nothing Then
                reader.Close()
                reader = Nothing
            End If
        End Try
        Return 0
    End Function
    Public Shared _FIELDS As String = "u.user_id AS uuser_id, u.department_id AS udepartment_id, u.location_id AS ulocation_id, u.username AS uusername, u.passwd AS upasswd, u.title AS utitle, " & _
                                      "u.full_name AS ufull_name, u.position AS uposition, u.phone AS uphone, u.mobile AS umobile, u.email AS uemail, u.active_code AS uactive_code, u.active AS uactive, " & _
                                      "u.company_id AS ucompany_id, u.modified_date AS umodified_date, u.modified_id AS umodified_id, u.synchronized AS usynchronized, u.deleted AS udeleted"
    Public Shared Function findByCriteria(ByVal _CompanyID As String, ByVal LocationID As String, ByVal SearchText As String, ByVal iActive As Integer, ByVal SortBy As String, ByVal bAsc As Boolean, ByVal iPageStart As Integer, ByVal iPageSize As Integer) As List(Of UserView)
        Dim index As Integer = 0
        Dim lParamArray As SqlParameter() = New SqlParameter(3) {}
        Dim _list As List(Of UserView) = Nothing

        Dim sql As String = ""
        Dim rows As Integer = 0
        If (iPageStart <= 1) Then
            iPageStart = 1
        Else
            rows = iPageSize * (iPageStart - 1)
        End If
        Dim aliasList As String = ""
        aliasList = "" & _FIELDS & ", " & DepartmentDAO._FIELDS & ", " & LocationDAO._FIELDS
        Dim condition As String = ""
        If _CompanyID <> "" Then
            condition &= "AND u.company_id=@CompanyID "
            lParamArray(index) = New SqlParameter("@CompanyID", _CompanyID)
            index = index + 1
        End If
        If LocationID <> "" Then
            condition &= "AND u.location_id=@LocationID "
            lParamArray(index) = New SqlParameter("@LocationID", LocationID)
            index = index + 1
        End If
        If SearchText <> "" Then
            condition &= "AND (" & _SEARCH_TEXT_CONDITION & ") "
            lParamArray(index) = New SqlParameter("@SearchText", "%" & SearchText & "%")
            index = index + 1
        End If
        If iActive >= 0 Then
            If iActive = 0 Then
                condition &= "AND u.active=0 "
            Else
                condition &= "AND u.active=1 "
            End If
        End If
        Dim sort As String = "u.full_name ASC"
        If SortBy <> "" Then
            sort = SortBy & " "
            If bAsc Then
                sort &= "ASC "
            Else
                sort &= "DESC "
            End If
        End If
        If iPageSize > 0 Then
            sql = "SELECT * FROM " & _
                  "(" & _
                  "SELECT " & aliasList & ", ROW_NUMBER() OVER (ORDER BY " & sort & ") AS Row " & _
                  "FROM " & _TableName & " u " & _
                  "LEFT OUTER JOIN " & DepartmentDAO._TableName & " d ON d.department_id=u.department_id " & _
                  "LEFT OUTER JOIN " & LocationDAO._TableName & " l ON u.location_id=l.location_id " & _
                  "WHERE u.deleted=0 " & condition & _
                  ") AS RecordView " & _
                  "WHERE Row > " & rows & " AND Row <= " & (rows + iPageSize) & " "
        Else
            sql = "SELECT " & aliasList & " " & _
                  "FROM " & _TableName & " u " & _
                  "LEFT OUTER JOIN " & DepartmentDAO._TableName & " d ON d.department_id=u.department_id " & _
                  "LEFT OUTER JOIN " & LocationDAO._TableName & " l ON u.location_id=l.location_id " & _
                  "WHERE u.deleted=0 " & condition & " ORDER BY " & sort
        End If
        Dim reader As SqlDataReader = Nothing
        Dim con As SqlConnection = Nothing
        Try
            con = DBHelper.GetConnection1()
            reader = SqlHelper.ExecuteReader(con, CommandType.Text, sql, lParamArray)
            If reader IsNot Nothing Then
                _list = New List(Of UserView)()
                While reader.Read
                    Dim strValue As New ArrayList()
                    strValue = DBHelper.ReadValue(reader)
                    Dim _rowObj As UserView = New UserView()
                    Dim indexStart As Integer = 0
                    _rowObj.User = getEntity(indexStart, strValue)
                    _rowObj.Department = DepartmentDAO.getEntity(indexStart, strValue)
                    _rowObj.Location = LocationDAO.getEntity(indexStart, strValue)
                    _list.Add(_rowObj)
                End While
            End If
        Catch ex As Exception
            Throw New Exception(ex.Message, ex)
        Finally
            If con IsNot Nothing Then
                con.Close()
                con.Dispose()
                con = Nothing
            End If
            If reader IsNot Nothing Then
                reader.Close()
                reader = Nothing
            End If
        End Try
        Return _list
    End Function
    Public Shared Function findByUsername(ByVal _Username As String, ByVal _Password As String) As User
        If _Username Is Nothing Or _Password Is Nothing Then
            Return Nothing
        End If
        If _Username.Trim = "" Or _Password.Trim = "" Then
            Return Nothing
        End If
        Dim con As SqlConnection = Nothing
        Dim reader As SqlDataReader = Nothing
        Dim sql As String = "SELECT * FROM " & _TableName & " " & _
                            "WHERE username=@Username AND passwd=HashBytes('MD5', '" & _Password & "') AND deleted=0 AND (active=1 OR user_id='" & CONSTANT.USER_ADMINISTRATOR_ID & "') "
        Try
            con = DBHelper.GetConnection1()
            Dim lParamArray As SqlParameter() = Nothing
            lParamArray = New SqlParameter() { _
                New SqlParameter("@Username", _Username) _
            }
            reader = SqlHelper.ExecuteReader(con, CommandType.Text, sql, lParamArray)
            If reader.Read Then
                Dim strValue As New ArrayList()
                strValue = DBHelper.ReadValue(reader)
                Return getEntity(0, strValue)
            End If
        Catch ex As Exception
            Throw New Exception(ex.Message, ex)
        Finally
            If con IsNot Nothing Then
                con.Close()
                con.Dispose()
                con = Nothing
            End If
            If reader IsNot Nothing Then
                reader.Close()
                reader = Nothing
            End If
        End Try

        Return Nothing
    End Function
    Public Shared Function findByUserCode(ByVal _Username As String) As User
        If _Username Is Nothing Then
            Return Nothing
        End If
        If _Username.Trim = "" Then
            Return Nothing
        End If
        Dim con As SqlConnection = Nothing
        Dim reader As SqlDataReader = Nothing
        Dim sql As String = "SELECT * FROM " & _TableName & " " & _
                            "WHERE username=@Username AND deleted=0 "
        Try
            con = DBHelper.GetConnection1()
            Dim lParamArray As SqlParameter() = Nothing
            lParamArray = New SqlParameter() { _
                New SqlParameter("@Username", _Username) _
            }
            reader = SqlHelper.ExecuteReader(con, CommandType.Text, sql, lParamArray)
            If reader.Read Then
                Dim strValue As New ArrayList()
                strValue = DBHelper.ReadValue(reader)
                Return getEntity(0, strValue)
            End If
        Catch ex As Exception
            Throw New Exception(ex.Message, ex)
        Finally
            If con IsNot Nothing Then
                con.Close()
                con.Dispose()
                con = Nothing
            End If
            If reader IsNot Nothing Then
                reader.Close()
                reader = Nothing
            End If
        End Try

        Return Nothing
    End Function
    Public Shared Function authorizedUserFromLocation(ByVal _Username As String, ByVal _Password As String, ByVal _LocationID As String) As Boolean
        If _Username Is Nothing Or _Password Is Nothing Or _LocationID Is Nothing Then
            Return False
        End If
        If _Username.Trim = "" Or _Password.Trim = "" Or _LocationID.Trim = "" Then
            Return Nothing
        End If
        Dim con As SqlConnection = Nothing
        Dim reader As SqlDataReader = Nothing
        Dim sql As String = "SELECT username FROM " & _TableName & " " & _
                            "WHERE username=@Username AND passwd=@Password AND location_id=@LocationID AND deleted=0 AND active=1 "
        Try
            con = DBHelper.GetConnection1()
            Dim lParamArray As SqlParameter() = Nothing
            lParamArray = New SqlParameter() { _
                New SqlParameter("@Username", _Username), _
                New SqlParameter("@Password", _Password), _
                New SqlParameter("@LocationID", _LocationID) _
            }
            reader = SqlHelper.ExecuteReader(con, CommandType.Text, sql, lParamArray)
            If reader.Read Then
                Dim strValue As New ArrayList()
                strValue = DBHelper.ReadValue(reader)
                Return (strValue(0) = _Username)
            End If
        Catch ex As Exception
            Throw New Exception(ex.Message, ex)
        Finally
            If con IsNot Nothing Then
                con.Close()
                con.Dispose()
                con = Nothing
            End If
            If reader IsNot Nothing Then
                reader.Close()
                reader = Nothing
            End If
        End Try

        Return False
    End Function
    Public Shared Function findByID(ByVal _ID As String, ByVal bCheckDeleted As Boolean) As User
        Dim con As SqlConnection = Nothing
        Dim reader As SqlDataReader = Nothing
        Dim sql As String = "SELECT * FROM " & _TableName & " " & _
                            "WHERE user_id=@UserID "
        If bCheckDeleted Then
            sql &= "AND deleted=0 "
        End If
        Try
            con = DBHelper.GetConnection1()
            Dim lParamArray As SqlParameter() = Nothing
            lParamArray = New SqlParameter() { _
                New SqlParameter("@UserID", _ID) _
            }
            reader = SqlHelper.ExecuteReader(con, CommandType.Text, sql, lParamArray)
            If reader.Read Then
                Dim strValue As New ArrayList()
                strValue = DBHelper.ReadValue(reader)
                Return getEntity(0, strValue)
            End If
        Catch ex As Exception
            Throw New Exception(ex.Message, ex)
        Finally
            If con IsNot Nothing Then
                con.Close()
                con.Dispose()
                con = Nothing
            End If
            If reader IsNot Nothing Then
                reader.Close()
                reader = Nothing
            End If
        End Try

        Return Nothing
    End Function
    Public Shared Function getEntity(ByRef indexStart As Integer, ByVal strValue As ArrayList) As User
        Dim obj As User = New User()
        obj.UserID = strValue(indexStart)
        indexStart += 1
        obj.DepartmentID = strValue(indexStart)
        indexStart += 1
        obj.LocationID = strValue(indexStart)
        indexStart += 1
        obj.UserName = strValue(indexStart)
        indexStart += 1
        obj.Passwd = strValue(indexStart)
        indexStart += 1
        obj.Title = strValue(indexStart)
        indexStart += 1
        obj.FullName = strValue(indexStart)
        indexStart += 1
        obj.Position = strValue(indexStart)
        indexStart += 1
        obj.Phone = strValue(indexStart)
        indexStart += 1
        obj.Mobile = strValue(indexStart)
        indexStart += 1
        obj.Email = strValue(indexStart)
        indexStart += 1
        obj.ActiveCode = strValue(indexStart)
        indexStart += 1
        obj.Active = strValue(indexStart)
        indexStart += 1
        obj.CompanyID = strValue(indexStart)
        indexStart += 1
        obj.ModifiedDate = strValue(indexStart)
        indexStart += 1
        obj.ModifiedID = strValue(indexStart)
        indexStart += 1
        obj.Synchronized = strValue(indexStart)
        indexStart += 1
        obj.Deleted = strValue(indexStart)
        indexStart += 1
        Return obj
    End Function
End Class
End Namespace
